package ch.zbw.pra.sysventory.database.jdbcTemplate;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.mysql.jdbc.Statement;

import ch.zbw.pra.sysventory.database.mapper.OperatingSystemMapper;
import ch.zbw.pra.sysventory.model.OperatingSystem;

/**
 * Greift auf die Datenbank zu (DB-Tabelle Operatingsystem) und delegiert das Mapping der Tabellenspalten an den RowMapper
 * Klasse kann auf die Datenbank schreiben, lesen und loeschen
 * 
 * @author Roberto Lucas
 */

public class OperatingSystemJDBCTemplate implements Serializable {
	
	private static final long serialVersionUID = -3399904525370289667L;
	private static Logger LOG = LoggerFactory.getLogger(OperatingSystemJDBCTemplate.class);
	private JdbcTemplate jdbcTemplateObject;
	
	private static final String INSERT_SQL = "INSERT INTO Operatingsystem (operName, operVersion,operBit, oper_compID_fk) VALUES(?,?,?,?)";
	
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);
	}
	public Integer insertOperatingSystem(final OperatingSystem operatingSystem, final Integer computerId) {
		KeyHolder keyHolder = new GeneratedKeyHolder();
		try{
		jdbcTemplateObject.update(new PreparedStatementCreator() { 
			public PreparedStatement createPreparedStatement(Connection connection) throws SQLException { 
				PreparedStatement ps = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS); 
					ps.setString(1, operatingSystem.getNameOperatingSystem()); 
					ps.setString(2, operatingSystem.getBuildOperatingSystem()); 
					ps.setString(3, operatingSystem.getArchitecture32Bit64BitOperatingSystem()); 
					ps.setInt(4, computerId);
					return ps; 
				} 
			}, keyHolder);
		}catch(DataAccessException e){
			LOG.debug("Failed to insert OperatingSystem for ComputerId "+computerId);
			LOG.debug(e.toString());
		}
		LOG.debug("Inserted software with id: " + keyHolder.getKey());
		return keyHolder.getKey().intValue();
	}

	public List<OperatingSystem> listOperatingSystem() {
		String SQL = "SELECT * FROM Operatingsystem GROUP BY operName,operVersion,operBit";
		List<OperatingSystem> operatingSystem = new ArrayList<OperatingSystem>();
		try{
			operatingSystem = jdbcTemplateObject.query(SQL,new OperatingSystemMapper());
		} catch(DataAccessException e){
			LOG.debug("Failed to load a list of OperatingSystems");
			LOG.error(e.toString());
		}
		LOG.debug("Loaded a list of OperatingSystems");
		return operatingSystem;
	}
	
	public OperatingSystem readOperatingSystemById(Integer operatingSystemId){
		String SQL = "SELECT * FROM Operatingsystem WHERE operID_pk = ?";
		OperatingSystem operatingSystem = new OperatingSystem();
		try{
			operatingSystem = jdbcTemplateObject.queryForObject(SQL, 
                    new Object[]{operatingSystemId}, new OperatingSystemMapper());
		} catch(DataAccessException e){
			LOG.debug("Failed to read OperatingSystem with id "+operatingSystemId);
			LOG.error(e.toString());
		}
	      LOG.debug("Read OperatingSystem with Id = "+ operatingSystem.getOperatingSystemId());
	      return operatingSystem;
	}
	
	public void deleteOperatingSystemById(Integer operatingSystemId) {
		String SQL = "DELETE FROM Operatingsystem WHERE operID_pk = ?";
		try{
			jdbcTemplateObject.update(SQL, operatingSystemId);
		} catch (DataAccessException e) {
			LOG.debug("Failed to delete OperatingSystem whit id "+operatingSystemId);
			LOG.error(e.toString());
		}
		LOG.debug("Deleted OperatingSystem with id = " + operatingSystemId);
	}
	
	public OperatingSystem readOperatingSystemFromComputer(Integer computerId) {
		String SQL = "SELECT * FROM Operatingsystem WHERE oper_compID_fk = ?";
		OperatingSystem operatingSystem = new OperatingSystem();
		try{
			operatingSystem = jdbcTemplateObject.queryForObject(SQL, 
                new Object[]{computerId}, new OperatingSystemMapper());
		} catch (DataAccessException e){
			LOG.debug("Failed to read OperatingSystem where Computer id is "+computerId);
			LOG.error(e.toString());
		}
		LOG.debug("Read OperatingSystem with Id = "+ operatingSystem.getOperatingSystemId());
		return operatingSystem;
	}
	
	

}

